package DataBase;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Vector;

import DataObjects.Bon;
import DataObjects.MetaDataOrder;

public class SaveTableOrder {
	
	private Vector<Bon> bones;
	private MetaDataOrder metaDataOrder;
	private int status=0;
	private String orderID;
	private int num;
	private int orderIdInt;
	
	public SaveTableOrder(Vector<Bon> b, MetaDataOrder m)
	{
		this.bones=b;
		this.metaDataOrder=m;
	}
	
	public void saveOrder()
	{
SQLConnection sc=new SQLConnection();
		
		try {
			sc.getConnection().setAutoCommit(false);
			if(metaDataOrder.isNew())				
			{
				PreparedStatement ps100 = sc.getConnection().prepareStatement("SELECT max(CAST(orderID AS UNSIGNED INTEGER))FROM timeslot ;");
				ResultSet rs100=ps100.executeQuery();
				while (rs100.next())
				{
					
					orderIdInt=rs100.getInt(1);

					if(orderIdInt==0){
						orderIdInt=0;
					}
								
			
				}
				
				
				orderIdInt++;
				PreparedStatement ps1 = sc.getConnection().prepareStatement("INSERT INTO timeslot(dateOpen,timeOpen,orderId) values(?,?,?);");
				ps1.setString(1,(String) metaDataOrder.getDate());
				ps1.setString(2,(String) metaDataOrder.getTime());
				//ps1.setString(3,(String)Integer.toString(orderIdInt));
				ps1.setInt(3,orderIdInt);
				ps1.executeUpdate();
				
				//nahodim otkrituyu mishmeret
				PreparedStatement psMishmeret = sc.getConnection().prepareStatement("SELECT  shiftId FROM shift WHERE status='open';");
				ResultSet rsMishmeret=psMishmeret.executeQuery();
				int shiftId=-1;
				if(rsMishmeret.next())
				{
					shiftId=rsMishmeret.getInt(1);
				}
				psMishmeret.close();
				rsMishmeret.close();
				
				PreparedStatement ps2 = sc.getConnection().prepareStatement("INSERT INTO table_timeslot(tid,dateOpen,status,no_of_persons,timeOpen,orderId,no_of_children,shiftId) values(?,?,?,?,?,?,?,?);");
				ps2.setString(1,(String) metaDataOrder.getTid());
				ps2.setString(2,(String) metaDataOrder.getDate());
				ps2.setString(3,"opened");
				ps2.setInt(4,metaDataOrder.getNumOfPersons());
				ps2.setString(5,(String) metaDataOrder.getTime());
				ps2.setInt(6,orderIdInt);
				ps2.setInt(7,metaDataOrder.getNumOfChild() );
				
				if(shiftId!=(-1))
				ps2.setInt(8,shiftId);
				
				ps2.executeUpdate();
				PreparedStatement ps3 = sc.getConnection().prepareStatement("INSERT INTO order_from_a_table(tid,dateOpen,rid,mid,indexNo,timeOpen,orderId,actionLevel,extraRid,extraName,souceRid,souceName,comments,isFired,isSubmitted) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
				for(int i=0 ; i<bones.size();i++)
				{
				ps3.setString(1,(String) metaDataOrder.getTid());
				ps3.setString(2,(String) metaDataOrder.getDate());
				ps3.setString(3,bones.get(i).getRid());
				ps3.setString(4,bones.get(i).getMid());
			
				ps3.setInt(5,i);
				ps3.setString(6,(String) metaDataOrder.getTime());
				ps3.setInt(7,orderIdInt);
				if(bones.get(i).getActionLevel()!=null)
				{
				ps3.setString(8, bones.get(i).getActionLevel());
				}
				
				else
				{
					ps3.setString(8,null);
					
				}

				if(bones.get(i).getExtra()!=null)
				{
				ps3.setInt(9, Integer.parseInt(bones.get(i).getExtra().getId()));
				ps3.setString(10, bones.get(i).getExtra().getName());
				}
				else
				{
					ps3.setInt(9,0);
					ps3.setString(10,null);
					
				}
				if(bones.get(i).getSouce()!=null)
				{
				ps3.setInt(11, Integer.parseInt(bones.get(i).getSouce().getId()));
				ps3.setString(12, bones.get(i).getSouce().getName());
				}
				else
				{
					ps3.setInt(11,0);
					ps3.setString(12,null);
					
				}
				ps3.setString(13, bones.get(i).getComments());
				ps3.setInt(14, bones.get(i).getIsFired());
				ps3.setInt(15, bones.get(i).getIsSubmitted());
				ps3.executeUpdate();
				}
											
				PreparedStatement ps4 = sc.getConnection().prepareStatement("INSERT INTO employee_table_timeslot(tid,dateOpen,eid,timeOpen,orderId) values(?,?,?,?,?);");
				ps4.setString(1,(String) metaDataOrder.getTid());
				ps4.setString(2,(String) metaDataOrder.getDate());
				ps4.setString(3,(String) metaDataOrder.getEid());
				ps4.setString(4,(String) metaDataOrder.getTime());
				ps4.setInt(5,orderIdInt);
				ps4.executeUpdate();
				
				ps1.close();
				ps2.close();
				ps3.close();
				ps4.close();
				sc.getConnection().commit();
				sc.getConnection().close();
				
				status=1;
				
					
				
			}
			
			else    // the order isn't new
			{
				

				PreparedStatement ps10 = sc.getConnection().prepareStatement("DELETE FROM order_from_a_table WHERE  tid=? AND dateOpen=? AND timeOpen=? ");
				
				 ps10.setString(1,(String) metaDataOrder.getTid());
				 ps10.setString(2,(String) metaDataOrder.getDate());
				 ps10.setString(3,(String) metaDataOrder.getTime());
				 ps10.executeUpdate();
				
				 PreparedStatement ps15 = sc.getConnection().prepareStatement("UPDATE table_timeslot SET no_of_persons=?,no_of_children=?  where orderId=? ");
				 ps15.setInt(1, metaDataOrder.getNumOfPersons());
				 ps15.setInt(2, metaDataOrder.getNumOfChild());
				 int orderId=Integer.parseInt(metaDataOrder.getOrderId());
				 ps15.setInt(3,orderId);
				 ps15.executeUpdate();
				 ps15.close();
				
					for(int i=0 ; i<bones.size();i++)
					{
						
					PreparedStatement ps11 = sc.getConnection().prepareStatement("INSERT INTO order_from_a_table(tid,dateOpen,rid,mid,indexNo,timeOpen,orderId,actionLevel,extraRid,extraName,souceRid,souceName,comments,isFired,isSubmitted) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);");
					ps11.setString(1,(String) metaDataOrder.getTid());
					ps11.setString(2,(String) metaDataOrder.getDate());
					ps11.setString(3,bones.get(i).getRid());
					ps11.setString(4,bones.get(i).getMid());
					
					ps11.setInt(5,i);
					ps11.setString(6,(String) metaDataOrder.getTime());
				
					ps11.setInt(7,Integer.parseInt(metaDataOrder.getOrderId()));
					
				
					
					if(bones.get(i).getActionLevel()!=null)
					{
					ps11.setString(8, bones.get(i).getActionLevel());
					}
					
					else
					{
						ps11.setString(8,null);
						
					}

					if(bones.get(i).getExtra()!=null)
					{
					ps11.setInt(9, Integer.parseInt(bones.get(i).getExtra().getId()));
					ps11.setString(10, bones.get(i).getExtra().getName());
					}
					else
					{
						ps11.setInt(9,0);
						ps11.setString(10,null);
						
					}
					if(bones.get(i).getSouce()!=null)
					{
					ps11.setInt(11, Integer.parseInt(bones.get(i).getSouce().getId()));
					ps11.setString(12, bones.get(i).getSouce().getName());
					}
					else
					{
						ps11.setInt(11,0);
						ps11.setString(12,null);
						
					}
					
					
					
					
					ps11.setString(13, bones.get(i).getComments());
					ps11.setInt(14, bones.get(i).getIsFired());
					ps11.setInt(15, bones.get(i).getIsSubmitted());
					ps11.executeUpdate();
					ps11.close();
					}
					
					ps10.close();
					sc.getConnection().commit();
					sc.getConnection().close();
					status=1;
					
			}
			
			
		
		}
		
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		}

	public int getStatus() {
		return status;
	}

	public void setStatus(int status) {
		this.status = status;
	}
	
	
	
	
	
	
	}


